<?php
// 完成tbl_topic表的操作数据
require_once "conn.php";

function get_time(): string {
    // 设置时区
    date_default_timezone_set("PRC");
    // 设置日期的格式
    $format = "Y-m-d H:i:s";
    // 获取当前的系统日期
    return date($format);
};
/**
 * 添加帖子信息，返回受影响行数
 * @param string $title 标题
 * @param string $content 内容
 * @param int $userid 用户id
 * @param int $boardid 版块id
 * @return mixed
 */
function add_topic($title, $content, $userid, $boardid) {
    $modifyITime = get_time();
    $sql = "insert into tbl_topic(title,content,modifyTime,uid,boardid,likes) values('$title','$content','$modifyITime',$userid,$boardid,0);";
    return exec_update($sql);
};

/**
 * 根据帖子id修改帖子
 * @param $topicid 帖子id
 * @param $title 标题
 * @param $content 内容
 * @return int
 */
function update_topic($topicid, $title, $content) {
    $modifyITime = get_time();
    $sql = "update tbl_topic set title='$title',content='$content',modifytime='$modifyITime' where topicid=$topicid";
    return exec_update($sql);
};

/**
 * 根据帖子编号删除帖子，返回受影响行数
 * @param int $topicid 帖子id
 * @return int
 */
function delete_topic($topicid) {
    $sql = "delete from tbl_topic where topicid=$topicid";
    return exec_update($sql);
};

/**
 * 根据帖子编号显示帖子信息(查3张表：版块表、用户表、帖子表),返回一维数组
 * @param int $topicid 帖子id
 * @return array
 */
function get_topics($topicid) {
    $sql = "SELECT *,(select count(*) from tbl_reply r where r.topicId=t.topicId) as replyNumber,(select count(*) from tbl_topic t1 where t1.uId=t.uId) as topicNumber FROM tbl_board b, tbl_topic t, tbl_user u WHERE b.boardid=t.boardId AND t.uId=u.uId and topicid=$topicid;
";
    $result = exec_select($sql);
    return $result[0];
};

/**
 * 统计某个版块的帖子数量,返回总数
 * @param  int $boardid 版块id
 * @return int
 */
function count_topics($boardid) {
    $sql = "select count(*) num from tbl_topic where boardid=$boardid";
    $result = exec_select($sql);
    return $result[0]['num'];
};

/**
 * 获取某个版块的最新帖子信息(查2张表：用户表、帖子表)
 * @param int $boardid 版块id
 * @return array
 */
function get_last_topic($boardid) {
    $sql = "select * from tbl_topic t,tbl_user u where t.uid=u.uid and boardid='$boardid' order by publishtime desc limit 1";
    $result = exec_select($sql);
    return $result[0];
};


/**
 * 根据版块编号获取所有帖子信息
 * @param int $boardid 版块id
 * @return array
 */
function get_all_topics($boardid) {
    $sql = "select * from tbl_topic t,tbl_user u where t.uid=u.uid and boardid='$boardid'";
    return exec_select ( $sql );
};
/**
 * 获取所有帖子信息包括发帖人信息
 * @return array
 */
function get_all_topics_info(){
    $sql="SELECT b.*,t.*,u.*,( SELECT COUNT(*) FROM tbl_reply r WHERE r.topicId = t.topicId ) AS replyNumber FROM tbl_board b,tbl_topic t,tbl_user u WHERE b.boardid = t.boardId AND t.uId = u.uId order by t.topicId DESC;";
    return exec_select($sql);
}
/**
 * 获取最热帖子信息
 * @return array
 */
function get_hot_topics_info(){
    $sql="SELECT b.*,t.*,u.*,( SELECT COUNT(*) FROM tbl_reply r WHERE r.topicId = t.topicId ) AS replyNumber FROM tbl_board b,tbl_topic t,tbl_user u WHERE b.boardid = t.boardId AND t.uId = u.uId order by t.likes DESC,t.publishTime DESC limit 10;";
    return exec_select($sql);
}
/**
 * 获取精华帖子信息
 * @return array
 */
function get_good_topics_info(){
    $sql="SELECT b.*, t.*, u.*, 
        (SELECT COUNT(*) FROM tbl_reply r WHERE r.topicId = t.topicId) AS replyNumber 
        FROM tbl_board b, tbl_topic t, tbl_user u 
        WHERE b.boardid = t.boardId 
          AND t.uId = u.uId 
          AND t.good = 1 
        ORDER BY t.publishTime DESC";
    return exec_select($sql);
}
/**
 * 通过关键词查询帖子标题或帖子内容包含关键词的帖子信息
 * @param mixed $search
 * @return array
 */
function get_search_topics_info($search){
    $sql="select *,(select boardName from tbl_board b where b.boardid=t.boardid) as boardName,(select count(*) from tbl_reply r where r.topicId=t.topicId) as replyNumber from tbl_topic t,tbl_user u where (t.title like '%$search%' or t.content like '%$search%') and t.uId=u.uId order by t.publishTime DESC;
";
return exec_select($sql);
}
/**
 * 获取活跃用户
 * @return array
 */
function get_active_users(){
    $sql="select u.uName,u.uId,u.head,(select count(*) from tbl_topic t where t.uId=u.uId) as topicNumber from tbl_user u order by topicNumber DESC;";
    return exec_select($sql);
}
/**
 * 添加评论
 * @param mixed $topicId
 * @param mixed $content
 * @param mixed $userId
 * @return mixed
 */
function add_reply($topicId, $content, $userId) {
    $modifyTime=get_time();
    $sql="insert into tbl_reply (title,content,modifyTime,uId,topicId) values('','$content','$modifyTime',$userId,$topicId);";
    return exec_update($sql);
}
/**
 * 通过帖子id获取帖子评论
 * @param mixed $topicId
 * @return array
 */
function get_reply($topicId) {
    $sql="select * from tbl_reply r,tbl_user u where r.topicId=$topicId and r.uId=u.uId order by r.publishTime DESC;";
    return exec_select($sql);
}
/**
 * 通过帖子id给帖子点个赞
 * @param mixed $topicId
 * @return mixed
 */
function like_topic($topicId){
    $sql="update tbl_topic set likes=likes+1 where topicId=$topicId";
    return exec_update($sql);
}

/**
 * 获取用户发布的所有帖子
 * @param int $userId 用户ID
 * @return array
 */
function get_user_topics($userId) {
    $sql = "SELECT b.boardName, t.*, u.uName, u.head, (SELECT COUNT(*) FROM tbl_reply r WHERE r.topicId = t.topicId) AS replyNumber 
            FROM tbl_board b, tbl_topic t, tbl_user u 
            WHERE b.boardid = t.boardId AND t.uId = u.uId AND t.uId = $userId 
            ORDER BY t.publishTime DESC";
    return exec_select($sql);
}

/**
 * 获取用户点赞过的帖子
 * @param int $userId 用户ID
 * @return array
 */
function get_user_liked_topics($userId) {
    $sql = "SELECT b.boardName, t.*, u.uName, u.head, (SELECT COUNT(*) FROM tbl_reply r WHERE r.topicId = t.topicId) AS replyNumber 
            FROM tbl_board b, tbl_topic t, tbl_user u, tbl_like l 
            WHERE b.boardid = t.boardId AND t.uId = u.uId AND l.topicId = t.topicId AND l.userId = $userId 
            ORDER BY l.likeTime DESC";
    return exec_select($sql);
}

/**
 * 获取用户回复过的帖子
 * @param int $userId 用户ID
 * @return array
 */
function get_user_replied_topics($userId) {
    $sql = "SELECT DISTINCT b.boardName, t.*, u.uName, u.head, (SELECT COUNT(*) FROM tbl_reply r WHERE r.topicId = t.topicId) AS replyNumber 
            FROM tbl_board b, tbl_topic t, tbl_user u, tbl_reply r 
            WHERE b.boardid = t.boardId AND t.uId = u.uId AND r.topicId = t.topicId AND r.uId = $userId 
            ORDER BY t.publishTime DESC";
    return exec_select($sql);
}

/**
 * 删除回复
 * @param int $replyId 回复ID
 * @return int
 */
function delete_reply($replyId) {
    $sql = "DELETE FROM tbl_reply WHERE replyId = $replyId";
    return exec_update($sql);
}

/**
 * 取消点赞
 * @param int $topicId 帖子ID
 * @param int $userId 用户ID
 * @return int
 */
function unlike_topic($topicId, $userId) {
    $sql = "DELETE FROM tbl_like WHERE topicId = $topicId AND userId = $userId";
    $result = exec_update($sql);
    if ($result) {
        $sql = "UPDATE tbl_topic SET likes = likes - 1 WHERE topicId = $topicId AND likes > 0";
        return exec_update($sql);
    }
    return 0;
}

/**
 * 检查用户是否点赞过某帖子
 * @param int $topicId 帖子ID
 * @param int $userId 用户ID
 * @return bool
 */
function check_user_liked($topicId, $userId) {
    $sql = "SELECT COUNT(*) as count FROM tbl_like WHERE topicId = $topicId AND userId = $userId";
    $result = exec_select($sql);
    return $result[0]['count'] > 0;
}

/**
 * 添加点赞记录
 * @param int $topicId 帖子ID
 * @param int $userId 用户ID
 * @return int
 */
function add_like_record($topicId, $userId) {
    $likeTime = get_time();
    $sql = "INSERT INTO tbl_like (topicId, userId, likeTime) VALUES ($topicId, $userId, '$likeTime')";
    return exec_update($sql);
}
?>